IF OBJECT_ID('ssf.gbl_disable_semaphore') IS NOT NULL
	DROP PROCEDURE ssf.gbl_disable_semaphore
GO

CREATE PROCEDURE ssf.gbl_disable_semaphore
	@semaphore_name NVARCHAR(128)
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@connection_count INT
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@semaphore_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[exit if semaphore does not exist]]></code_comment>*/
IF ssf.gbl_exists_variable(@semaphore_name) = 0
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[remove all connections]]></code_comment>*/
DECLARE @connections TABLE (
	semaphore_connection_id INT
)

INSERT @connections (
	semaphore_connection_id
)
SELECT semaphore_connection_id
FROM ssf.gbl_semaphore_connection WITH (NOLOCK)
WHERE semaphore_name = @semaphore_name

SET @connection_count = @@ROWCOUNT

BEGIN TRANSACTION

BEGIN TRY
	DELETE g
	FROM ssf.gbl_semaphore_connection g
	JOIN @connections c
	ON g.semaphore_connection_id = c.semaphore_connection_id
	WHERE g.locked_by_spid IS NULL

	SELECT @row_count = @@ROWCOUNT
		, @sql_error = @@ERROR

	IF @sql_error <> 0
	OR @row_count <> @connection_count
		RAISERROR('Failed to disable all connections for the semaphore <%s>.', 16, 1, @semaphore_name)
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END CATCH

COMMIT TRANSACTION
-- ============

RETURN @@ERROR
GO
